<?php

require_once('class.db.php');

class GeocodeManager {
	
	private $geocodes;
	
	public function getGeocodes() { 
		return $this->geocodes;
	}
	
	private static function queryMissingGeocodes(&$array) {
		
		$db = new DB();
		$result = $db->setQuery(	 
			"select address1, city, zip,  count(*) from meetings where "
				."concat(address1, city, zip) not in (select concat(address1, city, zip) "
				."from geocode) group by address1, city, zip");
			
		$dberr = $db->getError(); 
		if ($dberr) { 
			return $dberr;
		}

		while ($row = $result->fetch_row()) {
			$elem = array(
					"address1" => $row[0],
					"city" => $row[1],
					"zip" => $row[2]
			);
			array_push($array, $elem);
		}
				
		return null;
	} 

	private static function queryOrphanedGeocodes(&$array) {

		$db = new DB();
		
		$result = $db->setQuery(	 
			"select address1, city, zip from geocode where concat(address1,city,zip)".
			"not in (select concat(address1,city,zip) from meetings)");
			
		$dberr = $db->getError(); 
		if ($dberr) { 
			return $dberr;
		}
		
		while ($row = $result->fetch_row()) {
			$elem = array(
					"address1" => $row[0],
					"city" => $row[1],
					"zip" => $row[2]
			);
			array_push($array, $elem);
		}
		
		return null;
	}
	
	private static function queryMeetingsCount($address1, $city, $zip, &$count) {
		
		$db = new DB();
		
		$result = $db->setQuery("select count(*) from meetings where address1 = \""
				.$address1."\" and city = \"".$city."\" and zip = \"".$zip."\"");
		
		$err = $db->getError();
		if ($err) {
			return $err;
		}
		
		$row = $result->fetch_row();
		$count = $row[0];
		
		return null;
	}

	private static function deleteGeocode($address1, $city, $zip) {

		$db = new DB();
		
		$db->setQuery("delete from geocode where address1 = \"".$address1."\" and city = \""
				.$city."\" and zip = \"".$zip."\"");
		
		$status = $db->getError();
		if ($status) {
			return $status;
		}
		
		return null;
	}

	private static function deleteOrphanedGeocodes() {
		
		$orphans = array();
			
		$status = GeocodeManager::queryOrphanedGeocodes($orphans);
		if ($status) {
			return "Error querying orphans: ".$status;
		}
			
		foreach ($orphans as $orphan) {
			$status = GeocodeManager::deleteGeocode($orphan['address1'],
					$orphan['city'], $orphan['zip']);
			if ($status) {
				return "Error deleting geocode: $status";
			}
		}
	
		return null;
	}
	
	private static function insertGeocode($address1, $city, $zip, &$lat, &$lng) { 
		
		$db = new DB();
		
		$db->setQuery("insert into geocode (address1, city, zip, lat, lng, updated) ".
				"values (\"$address1\", \"$city\", \"$zip\", $lat, $lng, NOW());");
		$dberr = $db->getError();
		if ($dberr) { 
			return $dberr;
		}

		return null;
	}

	private static function queryGeocode($address1, $city, $zip, &$lat, &$lng) {
		
		$db = new DB();
		
		$result = $db->setQuery("select lat, lng from geocode where "
				."address1 = '$address1' and city = '$city' and zip = '$zip'");
		$dberr = $db->getError();

		if ($dberr) {
			return $dberr;
		}
		
		if ($row = $result->fetch_row()) { 
			$lat = $row[0];
			$lng = $row[1];
		} else { 
			$lat = null;
			$lng = null;
		}
		
		return null;
	}
	
	public function meetingAdded($address1, $city, $zip) {
		
		$status = GeocodeManager::queryGeocode($address1, $city, $zip, $lat, $lng);
		if ($status) {
			return "Error querying geocode: $status";
		}
			
		if ($lat) {
			return null; // already there no need to add
		}
		
		$status = GeocodeManager::geocodeLocation($address1, $city, $zip, $lat, $lng);
		if ($status != "OK") {
			return "Error geocoding address: $status";
		}
		
		$status = GeocodeManager::insertGeocode($address1, $city, $zip, $lat, $lng);
		if ($status) {
			return "Error inserting geocode: $status";
		}
		
		return null;
	}

	public function meetingUpdated($address1, $city, $zip) {
	
	
		$status = GeocodeManager::queryGeocode($address1, $city, $zip, $lat, $lng);
		if ($status) {
			return "Error querying geocode: $status";
		}
			
		if (!$lat) {
	
			$status = GeocodeManager::geocodeLocation($address1, $city, $zip, 
					$lat, $lng);
			if ($status != "OK") {
				return "Error geocoding address: $status";
			}
		
			$status = GeocodeManager::insertGeocode($address1, $city, $zip, 
					$lat, $lng);
			if ($status) {
				return "Error inserting geocode: $status";
			}
		}
		
		// address may have changed and now there may be orphans, have to clean them up
		$status = GeocodeManager::deleteOrphanedGeocodes();
		
		return $status;
	}
	
	public function meetingDeleted($address1, $city, $zip) {

		$status = GeocodeManager::queryMeetingsCount($address1, $city, $zip, $count);
		if ($status) {
			return "Error querying meeting count: $status";
		}
		
		if ($count == 0) {
			// no more meetings rows so delete the geocode row
			$status = GeocodeManager::deleteGeocode($address1, $city, $zip);
			if ($status) {
				return "Error deleting geocode: $status";
			}
		}
		
		return null;
	}
	
	public function updateGeocodes($rebuild = false) { 
		
		$db = new DB();
		
		if ($rebuild) {
			$db->setQuery("delete from geocode");
			$err = $db->getError();
			if ($err) {
				return "Error deleting geocode table $err";
			}
		}
		
		$this->geocodes = array();
		$err = GeocodeManager::queryMissingGeocodes($this->geocodes);
		if ($err) { 
			return "Error querying geocodes $err";
		}
		
		foreach ($this->geocodes as &$geocode) {
			
			$status = GeocodeManager::geocodeLocation($geocode['address1'], 
					$geocode['city'], $geocode['zip'], $geocode['lat'], $geocode['lng']);
			
			if ($status != "OK") {
				$geocode['status'] = $status;
			} else {
				
			 	$status = GeocodeManager::insertGeocode($geocode['address1'],
			 			$geocode['city'], $geocode['zip'], $geocode['lat'], 
			 			$geocode['lng']);
			 	if ($status) {
			 		$geocode['status'] = $status;
			 	} else {
			 		$geocode['status'] = "OK";
			 	}
			}
		}

		if (!$rebuild) {
			// rebuild deletes all geocodes so this wouldn't be necessary in that case
			$status = GeocodeManager::deleteOrphanedGeocodes();
			if ($status) {
				return $status;
			}
		}
		
		return null;
	}

	public static function concatAddress($address, $city, $zip) {
	
		$fulladdr = "";
	
		$address = trim($address);
		$city = trim($city);
		$zip = trim($zip);
	
		if (strlen($address)) {
			$fulladdr .= $address;
		}
	
		if (strlen($city) > 0) {
			if (strlen($fulladdr) > 0) {
				$fulladdr .= ", ";
			}
			$fulladdr .= $city;
		}
	
		if ((strlen($fulladdr) > 0) || (strlen($zip) > 0)) {
			if (strlen($fulladdr) > 0) {
				$fulladdr .= ", ";
			}
			$fulladdr .= "GA";
		}
	
		if (strlen($zip) > 0) {
			$fulladdr .= ", " . $zip;
		}
	
		return $fulladdr;
	}
	
	
	public static function geocodeLocation($address1, $city, $zip, &$lat, &$lng) {
		
		$address = GeocodeManager::concatAddress($address1, $city, $zip);
		$address = preg_replace("/\s+/", "+", $address);

		if (strlen($address) == 0) { 
			return "Nothing to do";
		}
		
		$request =  "http://maps.googleapis.com/maps/api/geocode/json?address=$address&sensor=false";

		$retries = 5;
		$obj = null; 
		while ($retries > 0) {
			
			$session = curl_init($request);
			curl_setopt($session, CURLOPT_HEADER, false);
			curl_setopt($session, CURLOPT_RETURNTRANSFER, true);
			$response = curl_exec($session);
			curl_close($session);
			
			$obj = json_decode($response);
			if ($obj) {
				if ($obj->status == "OK") {
					$lat = $obj->results[0]->geometry->location->lat;
					$lng = $obj->results[0]->geometry->location->lng;
					break;
				
				} else if ($obj->status != "OVER_QUERY_LIMIT" && 
						$obj->status != "UNKNOWN_ERROR" ) {
					break;	
				}
			}			
			--$retries;
			sleep(1);
		}
		
		if ($obj) {
			return $obj->status;
		} else 
			return "Null object returned from geocoding call";
		}	
	}

?>